package domain.service.rate;

import domain.dao.rate.RateInfoDao;
import domain.entity.rate.RateInfo;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.text.NumberFormat;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * Created by bowen on 2017-08-30.
 */
@Service
public class RateService {

    @Autowired
    private RateInfoDao infoDao;

    public List<Map<String, Object>> executeSql(String sql) {
        return infoDao.executeSql(sql);
    }

    public List<RateInfo> GetRateInfo1() {
        List<RateInfo> list = new ArrayList<RateInfo>();
        list.add(GetRateInfo("一标段"));
        list.add(GetRateInfo("二标段"));
        list.add(GetRateInfo("三标段"));
        return list;
    }

    private RateInfo GetRateInfo(String bd) {
        RateInfo RateInfo = new RateInfo();
        RateInfo.setItem(bd);
        double _RateInfo = 0.00;
        List<RateInfo> _list = GetRateInfo2(bd);
        for (int i = 0; i < _list.size(); i++) {
            _RateInfo += Double.parseDouble(_list.get(i).getRate().substring(0, _list.get(i).getRate().length() - 1));
        }
        RateInfo.setRate(formatRate(doubleToStr((_RateInfo / _list.size()) / 100)));
        return RateInfo;
    }

    public List<RateInfo> GetRateInfo2(String bd) {
        List<RateInfo> list = new ArrayList<RateInfo>();
        if (bd.equals("一标段")) {
            list.add(GetRateInfo(bd, "桥梁"));
            list.add(GetRateInfo(bd, "箱涵"));
            list.add(GetRateInfo(bd, "植物园隧道"));
        }
        if (bd.equals("二标段")) {
            list.add(GetRateInfo(bd, "桥梁"));
            list.add(GetRateInfo(bd, "道路"));
        }
        if (bd.equals("三标段")) {
            list.add(GetRateInfo(bd, "南山隧道"));
            list.add(GetRateInfo(bd, "劳动路立交"));
            list.add(GetRateInfo(bd, "建国路立交"));
            list.add(GetRateInfo(bd, "湟中路立交"));
        }
        return list;
    }

    private RateInfo GetRateInfo(String bd, String item) {
        RateInfo RateInfo = new RateInfo();
        RateInfo.setItem(item);
        double _RateInfo = 0.00;
        List<RateInfo> _list = GetRateInfo3(bd, item);
        for (int i = 0; i < _list.size(); i++) {
            _RateInfo += Double.parseDouble(_list.get(i).getRate().substring(0, _list.get(i).getRate().length() - 1));
        }
        RateInfo.setRate(formatRate(doubleToStr((_RateInfo / _list.size()) / 100)));
        return RateInfo;
    }

    public List<RateInfo> GetRateInfo3(String bd, String item) {
        String sql = "";
        List<RateInfo> list = new ArrayList<RateInfo>();
        if (bd.equals("一标段")) {
            if (item.equals("桥梁")) {
                sql = "select e.c2 as c3,(sum(e.finishqty)/sum(e.designqty)) rate,sum(e.finishqty) finishqty,sum(e.designqty) designqty from  ( select d.c2,d.rate,d.finishqty,d.designqty from  (select c.bd,c.prj,c.c1,c.c2,c.c3,nvl(c.finishqty,0) finishqty,c.designqty,nvl(c.rate,0) rate from  (select a.bd,a.prj,a.c1,a.c2,a.c3, (select count(*) from Road b where b.bd=a.bd and b.prj=a.prj and b.c1=a.c1 and b.c2=a.c2 and nvl(b.c3,'1')=nvl(a.c3,'1') and b.endtime is not null) finishqty,  a.designqty, ((select count(*) from Road b where b.bd=a.bd and b.prj=a.prj and b.c1=a.c1 and b.c2=a.c2 and nvl(b.c3,'1')=nvl(a.c3,'1') and b.endtime is not null)/a.designqty) rate  from Road a where a.bd='一标段' and a.prj='桥梁'  ) c group by c.bd,c.prj,c.c1,c.c2,c.c3,c.finishqty,c.designqty,c.rate order by c.bd,c.prj,c.c1,c.c2,c.c3) d  union all select d.c3,d.rate,d.finishqty,d.designqty from  (select c.bd,c.prj,c.c1,c.c2,c.c3,nvl(c.finishqty,0) finishqty,c.designqty,nvl(c.rate,0) rate from  (select a.bd,a.prj,a.c1,a.c2,a.c3, (select count(*) from Road b where b.bd=a.bd and b.prj=a.prj and b.c1=a.c1 and b.c2=a.c2 and nvl(b.c3,'1')=nvl(a.c3,'1') and b.endtime is not null) finishqty,  a.designqty, ((select count(*) from Road b where b.bd=a.bd and b.prj=a.prj and b.c1=a.c1 and b.c2=a.c2 and nvl(b.c3,'1')=nvl(a.c3,'1') and b.endtime is not null)/a.designqty) rate  from Road a where a.bd='一标段' and a.c1='桥梁' ) c group by c.bd,c.prj,c.c1,c.c2,c.c3,c.finishqty,c.designqty,c.rate order by c.bd,c.prj,c.c1,c.c2,c.c3  ) d  ) e group by e.c2 ";
            }
            if (item.equals("箱涵")) {
                sql = "select b.c2 as c3,b.rate,b.finishqty,b.designqty from (select a.bd,a.c1,a.c2,sum(nvl(a.sumqty,0)) finishqty,sum(a.designqty) designqty,(sum(nvl(a.sumqty,0))/sum(a.designqty)) rate from road a where a.bd='一标段' and a.c1='箱涵' group by a.bd,a.c1,a.c2 order by a.bd,a.c1,a.c2) b order by b.c2 ";
            }
            if (item.equals("植物园隧道")) {
                sql = "select (d.c1 || d.c2) as c3,d.rate,d.finishqty/1000,d.designqty/1000 from  (select c.bd,c.prj,c.c1,c.c2,c.c3,nvl(c.finishqty,0) finishqty,c.designqty,nvl(c.rate,0) rate from  (select a.bd,a.prj,a.c1,a.c2,a.c3, (select sum(TO_NUMBER(SUBSTR(b.endlc,INSTR(b.endlc,'+', 1, 1))))-sum(TO_NUMBER(SUBSTR(b.beginlc,INSTR(b.beginlc,'+', 1, 1))))  from Road b where b.bd=a.bd and b.prj=a.prj and b.c1=a.c1 and b.c2=a.c2 and nvl(b.c3,'1')=nvl(a.c3,'1') and b.endtime is not null) finishqty,  a.designqty, ((select sum(TO_NUMBER(SUBSTR(b.endlc,INSTR(b.endlc,'+', 1, 1))))-sum(TO_NUMBER(SUBSTR(b.beginlc,INSTR(b.beginlc,'+', 1, 1))))  from Road b where b.bd=a.bd and b.prj=a.prj and b.c1=a.c1 and b.c2=a.c2 and nvl(b.c3,'1')=nvl(a.c3,'1') and b.endtime is not null)/a.designqty) rate  from Road a where a.bd='一标段' and a.prj='植物园隧道' ) c group by c.bd,c.prj,c.c1,c.c2,c.c3,c.finishqty,c.designqty,c.rate order by c.bd,c.prj,c.c1,c.c2,c.c3  ) d ";
            }
        }
        if (bd.equals("二标段")) {
            if (item.equals("桥梁")) {
                sql = "select e.c2 as c3,e.rate,e.finishqty,e.designqty from ( select d.bd,d.prj,d.c2,sum(d.finishqty) finishqty,sum(d.designqty) designqty,(sum(d.finishqty)/sum(d.designqty)) rate from ( select c.bd,c.prj,c.c1,c.c2,c.c3,nvl(c.finishqty,0) finishqty,c.designqty,nvl(c.rate,0) rate from  (select a.bd,a.prj,a.c1,a.c2,a.c3, (select count(*) from Road b where b.bd=a.bd and b.prj=a.prj and b.c1=a.c1 and b.c2=a.c2 and nvl(b.c3,'1')=nvl(a.c3,'1') and  b.endtime is not null) finishqty,  a.designqty, ((select count(*) from Road b where b.bd=a.bd and b.prj=a.prj and b.c1=a.c1 and b.c2=a.c2 and nvl(b.c3,'1')=nvl(a.c3,'1') and  b.endtime is not null)/a.designqty) rate  from Road a where a.bd='二标段' and a.prj='桥梁') c group by c.bd,c.prj,c.c1,c.c2,c.c3,c.finishqty,c.designqty,c.rate order by c.bd,c.prj,c.c1,c.c2,c.c3  ) d group by d.bd,d.prj,d.c2 order by d.bd,d.prj,d.c2 ) e ";
            }
            if (item.equals("道路")) {
                sql = "select d.c3,(sum(d.finishqty)/sum(d.designqty)) rate,sum(d.finishqty)/1000 finishqty,sum(d.designqty)/1000 designqty from  (select c.bd,c.prj,c.c1,c.c2,c.c3,nvl(c.finishqty,0) finishqty,c.designqty,nvl(c.rate,0) rate from  (select a.bd,a.prj,a.c1,a.c2,a.c3, (select sum(TO_NUMBER(SUBSTR(b.endlc,INSTR(b.endlc,'+', 1, 1))))-sum(TO_NUMBER(SUBSTR(b.beginlc,INSTR(b.beginlc,'+', 1, 1))))  from Road b where b.bd=a.bd and b.prj=a.prj and b.c1=a.c1 and b.c2=a.c2 and nvl(b.c3,'1')=nvl(a.c3,'1') and b.endtime is not null) finishqty,  a.designqty, ((select sum(TO_NUMBER(SUBSTR(b.endlc,INSTR(b.endlc,'+', 1, 1))))-sum(TO_NUMBER(SUBSTR(b.beginlc,INSTR(b.beginlc,'+', 1, 1))))  from Road b where b.bd=a.bd and b.prj=a.prj and b.c1=a.c1 and b.c2=a.c2 and nvl(b.c3,'1')=nvl(a.c3,'1') and b.endtime is not null)/a.designqty) rate  from Road a where a.bd='二标段' and a.c1='道路' ) c group by c.bd,c.prj,c.c1,c.c2,c.c3,c.finishqty,c.designqty,c.rate order by c.bd,c.prj,c.c1,c.c2,c.c3 ) d group by d.c3 ";
            }
        }
        if (bd.equals("三标段")) {
            if (item.equals("南山隧道")) {
                sql = "select (d.c1 || d.c2) as c3,(d.finishqty/d.designqty) rate,d.finishqty/1000,d.designqty/1000 from  (select c.bd,c.prj,c.c1,c.c2,nvl(c.finishqty,0) finishqty,c.designqty from  ( select a.bd,a.prj,a.c1,a.c2,a.c3, (select sum(TO_NUMBER(SUBSTR(b.endlc,INSTR(b.endlc,'+', 1, 1))))-sum(TO_NUMBER(SUBSTR(b.beginlc,INSTR(b.beginlc,'+', 1, 1))))  from Road b where b.bd=a.bd and b.prj=a.prj and b.c1=a.c1 and b.c2=a.c2 and nvl(b.c3,'1')=nvl(a.c3,'1') and b.endtime is not null) finishqty,  a.designqty, ((select sum(TO_NUMBER(SUBSTR(b.endlc,INSTR(b.endlc,'+', 1, 1))))-sum(TO_NUMBER(SUBSTR(b.beginlc,INSTR(b.beginlc,'+', 1, 1))))  from Road b where b.bd=a.bd and b.prj=a.prj and b.c1=a.c1 and b.c2=a.c2 and nvl(b.c3,'1')=nvl(a.c3,'1') and b.endtime is not null)/a.designqty) rate from Road a  where a.bd='三标段' and a.prj='南山隧道') c group by c.bd,c.prj,c.c1,c.c2,finishqty,c.designqty order by c.bd,c.prj,c.c1,c.c2) d ";
            }
            if (item.equals("劳动路立交")) {
                sql = "select c.c3,nvl(c.rate,0) rate,c.finishqty,c.designqty from  (select a.bd,a.prj,a.c1,a.c2,a.c3, (select count(*) from Road b where b.bd=a.bd and b.prj=a.prj and b.c1=a.c1 and b.c2=a.c2 and nvl(b.c3,'1')=nvl(a.c3,'1') and b.endtime is not null) finishqty,  a.designqty, ((select count(*) from Road b where b.bd=a.bd and b.prj=a.prj and b.c1=a.c1 and b.c2=a.c2 and nvl(b.c3,'1')=nvl(a.c3,'1') and b.endtime is not null)/a.designqty) rate  from Road a where a.bd='三标段' and a.prj='劳动路立交' and a.c1<>'箱涵' ) c group by c.bd,c.prj,c.c1,c.c2,c.c3,c.finishqty,c.designqty,c.rate order by c.bd,c.prj,c.c1,c.c2,c.c3 ";
            }
            if (item.equals("建国路立交")) {
                sql = "select d.c3,(sum(d.finishqty)/sum(d.designqty)) rate,sum(d.finishqty) finishqty,sum(d.designqty) designqty from( select c.bd,c.prj,c.c1,c.c2,c.c3,nvl(c.finishqty,0) finishqty,c.designqty,nvl(c.rate,0) rate from  ( select a.bd,a.prj,a.c1,a.c2,a.c3, (case nvl(a.code,'-') when '-' then  (select (sum(TO_NUMBER(SUBSTR(b.endlc,INSTR(b.endlc,'+', 1, 1))))-sum(TO_NUMBER(SUBSTR(b.beginlc,INSTR(b.beginlc,'+', 1, 1)))))/1000  from Road b where b.bd=a.bd and b.prj=a.prj and b.c1=a.c1 and b.c2=a.c2 and nvl(b.c3,'1')=nvl(a.c3,'1') and b.endtime is not null)  else (case a.c1 when '箱涵' then (select TO_NUMBER(b.sumqty) from Road b where b.bd=a.bd and b.prj=a.prj  and b.c1=a.c1 and b.c2=a.c2 and nvl(b.c3,'1')=nvl(a.c3,'1') and b.endtime is not null)  else (select count(*) from Road b where b.bd=a.bd and b.prj=a.prj  and b.c1=a.c1 and b.c2=a.c2 and nvl(b.c3,'1')=nvl(a.c3,'1') and b.endtime is not null) end) end) finishqty,  (case nvl(a.code,'-') when '-' then TO_NUMBER(a.designqty)/1000 else TO_NUMBER(a.designqty) end) designqty, (case nvl(a.code,'-') when '-' then  ((select (sum(TO_NUMBER(SUBSTR(b.endlc,INSTR(b.endlc,'+', 1, 1))))-sum(TO_NUMBER(SUBSTR(b.beginlc,INSTR(b.beginlc,'+', 1, 1)))))/1000  from Road b where b.bd=a.bd and b.prj=a.prj  and b.c1=a.c1 and b.c2=a.c2 and nvl(b.c3,'1')=nvl(a.c3,'1') and b.endtime is not null)/a.designqty)  else ((case a.c1 when '箱涵' then (select TO_NUMBER(b.sumqty) from Road b where b.bd=a.bd and b.prj=a.prj and b.c1=a.c1 and b.c2=a.c2 and nvl(b.c3,'1')=nvl(a.c3,'1') and b.endtime is not null)  else (select count(*) from Road b where b.bd=a.bd and b.prj=a.prj  and b.c1=a.c1 and b.c2=a.c2 and nvl(b.c3,'1')=nvl(a.c3,'1') and b.endtime is not null) end)/a.designqty) end) rate  from Road a where a.bd='三标段' and a.prj='建国路立交' ) c group by c.bd,c.prj,c.c1,c.c2,c.c3,c.finishqty,c.designqty,c.rate order by c.bd,c.prj,c.c1,c.c2,c.c3  ) d group by d.c3 order by d.c3 ";
            }
            if (item.equals("湟中路立交")) {
                sql = "select d.c3,d.rate,d.finishqty,d.designqty from (select c.bd,c.prj,c.c1,c.c2,c.c3,nvl(c.finishqty,0) finishqty,c.designqty,nvl(c.rate,0) rate from  (select a.bd,a.prj,a.c1,a.c2,a.c3, (select count(*) from Road b where b.bd=a.bd and b.prj=a.prj and b.c1=a.c1 and b.c2=a.c2 and nvl(b.c3,'1')=nvl(a.c3,'1') and  b.endtime is not null) finishqty,  a.designqty, ((select count(*) from Road b where b.bd=a.bd and b.prj=a.prj and b.c1=a.c1 and b.c2=a.c2 and nvl(b.c3,'1')=nvl(a.c3,'1') and  b.endtime is not null)/a.designqty) rate  from Road a where a.bd='三标段' and a.prj='湟中路立交') c group by c.bd,c.prj,c.c1,c.c2,c.c3,c.finishqty,c.designqty,c.rate order by c.bd,c.prj,c.c1,c.c2,c.c3) d ";
            }
        }
        try {
            List<Map<String, Object>> _list = infoDao.executeSql(sql);
            for (int i = 0; i < _list.size(); i++) {
                RateInfo RateInfo = new RateInfo();
                Map<String, Object> resultMap = _list.get(i);
                RateInfo.setItem(resultMap.get("C3").toString());
                RateInfo.setRate(formatRate(resultMap.get("RATE").toString()));
                if(resultMap.containsKey("FINISHQTY")){
                    RateInfo.setFinishqty(resultMap.get("FINISHQTY").toString());
                }
                if(resultMap.containsKey("DESIGNQTY")){
                    RateInfo.setDesignqty(resultMap.get("DESIGNQTY").toString());
                }
                list.add(RateInfo);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }

    public String formatRate(String rate) {
        if (rate != null && rate != "") {
            int i = rate.indexOf(".");
            if (i > 0) {
                String _rate = "";
                if (!rate.substring(0, i).equals("0")) {
                    return "100";
                } else {
                    if ((rate.length() - i) < 6) {
                        int j = 6 - (rate.length() - i);
                        for (int k = 0; k < j; k++) {
                            rate += "0";
                        }
                    }
                    _rate += rate.substring(i + 1, i + 2).replace("0", "");
                    if (rate.substring(i + 2, i + 3) != null && rate.substring(i + 2, i + 3) != "") {
                        _rate += rate.substring(i + 2, i + 3);
                    } else {
                        _rate += "0";
                    }
                    _rate += ".";
                    if (rate.substring(i + 3, i + 6) != null && rate.substring(i + 3, i + 6) != "") {
                        _rate += rate.substring(i + 3, i + 6);
                    } else {
                        _rate += "000";
                    }
                    return _rate;
                }
            } else {
                if (rate.substring(0, 1).equals("0")) {
                    return "0.000";
                } else {
                    return "100";
                }
            }
        } else {
            return "0.00";
        }
    }

    private static String doubleToStr(Double d) {
        if (d == null) {
            return "";
        }
        NumberFormat nf = NumberFormat.getInstance();
        nf.setMaximumFractionDigits(6);//小数显示最多位数超出四舍五入
        nf.setGroupingUsed(false);//不分割
        return (nf.format(d));
    }

}
